﻿using System;
using System.Collections.Generic;
using System.Web;
using CarlosAg.ExcelXmlWriter;
using System.Data;
using System.Linq;
using System.Web.DynamicData;
using System.Reflection;
using System.Web.UI.WebControls;
using System.Data.Objects.DataClasses;
using System.Collections;
using System.Web.UI;
using System.Web.UI.WebControls.Expressions;

namespace NotAClue.Web.DynamicData
{
    /// <summary>
    /// Populates an Excel Worksheet based on the results of a Linq Query
    /// </summary>
    public class PopulateExcelWorksheetForDynamicData
    {
        Worksheet Sheet;

        private const string EXCEL_DATE_FORMAT = @"yyyy-MM-dd\THH:mm:ss.fff"; // i.e. 2003-05-10T08:25:00.000

        public PopulateExcelWorksheetForDynamicData(Worksheet Sheet)
        {
            this.Sheet = Sheet;
        }

        public void Populate(IQueryable queryResult, MetaTable table, Boolean showAllColumns)
        {
            WorksheetRow row = null;

            try
            {
                // Generate header row
                row = Sheet.Table.Rows.Add();
                var metaCols = GetColumns(table, showAllColumns);
                foreach (var metaCol in metaCols)
                {
                    row.Cells.Add(new WorksheetCell(metaCol.ShortDisplayName, "HeaderStyle"));
                }

                // Generate data rows
                foreach (var resultRow in queryResult)
                {
                    row = Sheet.Table.Rows.Add();

                    foreach (var metaCol in metaCols)
                    {
                        DataType dataType = DataType.String;
                        string resultColStr = string.Empty;

                        var fkCol = metaCol as MetaForeignKeyColumn;
                        if (fkCol != null)
                        {
                            var relatedEntityMethodInfo = resultRow.GetType().GetProperty(metaCol.Name);
                            var relatedEntity = relatedEntityMethodInfo.GetValue(resultRow, null);

                            if (relatedEntity != null)
                            {
                                var displayColumnPropInfo = relatedEntity.GetType().GetProperty(fkCol.ParentTable.DisplayColumn.Name);
                                var colValue = displayColumnPropInfo.GetValue(relatedEntity, null);
                                dataType = DetermineFormat(fkCol.ParentTable.DisplayColumn);

                                resultColStr = ConvertToString(colValue, dataType);
                            }

                        }
                        else
                        {
                            var colPropInfo = resultRow.GetType().GetProperty(metaCol.Name);
                            var colValue = colPropInfo.GetValue(resultRow, null);
                            dataType = DetermineFormat(metaCol);

                            resultColStr = ConvertToString(colValue, dataType);
                        }

                        string style = GetCellStyle(dataType);
                        if (String.IsNullOrEmpty(resultColStr))
                            row.Cells.Add(new WorksheetCell(resultColStr, DataType.String));
                        else if (String.IsNullOrEmpty(style))
                            row.Cells.Add(new WorksheetCell(resultColStr, dataType));
                        else
                            row.Cells.Add(new WorksheetCell(resultColStr, dataType, style));
                    }

                }
            }
            finally
            {

            }
        }

        private string GetCellStyle(DataType dataType)
        {
            if (dataType == DataType.DateTime)
            {
                return "DateStyle";
            }
            else
            {
                return string.Empty;
            }
        }

        private string ConvertToString(object resultCol, DataType dataType)
        {
            string resultColStr = string.Empty;
            if (dataType == DataType.DateTime)
            {
                var dateVal = resultCol as DateTime?;
                resultColStr = resultCol == null ? string.Empty : dateVal.Value.ToString(EXCEL_DATE_FORMAT);
            }
            else if (dataType == DataType.Boolean)
            {
                if (resultCol == null) { resultColStr = string.Empty; }
                else if (resultCol != null)
                {
                    var boolVal = resultCol as bool?;
                    resultColStr = (boolVal.Value) ? "1" : "0";
                }
            }
            else
            {
                resultColStr = resultCol == null ? string.Empty : Convert.ToString(resultCol);
            }
            return resultColStr;
        }

        public List<MetaColumn> GetColumns(MetaTable table, Boolean showAllColumns)
        {
            List<MetaColumn> list = new List<MetaColumn>();

            IEnumerable<MetaColumn> metaCols;
            if (showAllColumns)
                metaCols = table.Columns.Where(c => c.Scaffold).ToList();
            else
                metaCols = table.GetScaffoldColumns(DataBoundControlMode.ReadOnly, ContainerType.List);

            foreach (MetaColumn column in metaCols)
            {
                if (!column.IsForeignKeyComponent && !(column is MetaChildrenColumn))
                    list.Add(column);
            }
            return list;
        }

        private string GetUserFriendlyColumnName(string columnName, MetaTable table)
        {
            MetaColumn column = table.GetColumn(columnName);
            return column.DisplayName;
        }

        public DataType DetermineFormat(MetaColumn column)
        {

            if (column.TypeCode == TypeCode.Boolean) return DataType.Boolean;
            else if (column.TypeCode == TypeCode.DateTime) return DataType.DateTime;
            else if (column.IsInteger || column.IsFloatingPoint) return DataType.Number;
            else if (column.IsString) return DataType.String;
            else return DataType.String;
        }

        public static void SetCannedStyle1(Workbook Book)
        {
            // Specify which Sheet should be opened and the size of window by default
            Book.ExcelWorkbook.ActiveSheetIndex = 1;
            Book.ExcelWorkbook.WindowTopX = 100;
            Book.ExcelWorkbook.WindowTopY = 200;
            Book.ExcelWorkbook.WindowHeight = 7000;
            Book.ExcelWorkbook.WindowWidth = 8000;

            // Some optional properties of the Document
            Book.Properties.Created = DateTime.Now;

            // Create the Default Style to use for everyone
            WorksheetStyle style = Book.Styles.Add("Default");
            style.Font.FontName = "Tahoma";
            style.Font.Size = 10;

            style = Book.Styles.Add("HeaderStyle");
            style.Font.FontName = "Tacoma";
            style.Font.Size = 10;
            style.Font.Bold = true;
            style.Font.Italic = false;
            style.Font.Color = "White";
            style.Interior.Color = "#507cd1";
            style.Interior.Pattern = StyleInteriorPattern.Solid;
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Alignment.WrapText = true;
            style.Alignment.Vertical = StyleVerticalAlignment.Top;

            style = Book.Styles.Add("DateStyle");
            style.Font.FontName = "Tacoma";
            style.Font.Size = 10;
            style.Font.Bold = false;
            style.Font.Italic = false;
            style.Alignment.Horizontal = StyleHorizontalAlignment.Left;
            style.Alignment.WrapText = false;
            style.Alignment.Vertical = StyleVerticalAlignment.Top;
            style.NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";

            style = Book.Styles.Add("FooterStyle");
            style.Font.FontName = "Tacoma";
            style.Font.Size = 10;
            style.Font.Bold = false;
            style.Font.Italic = false;
            style.Font.Color = "White";
            style.Interior.Color = "#507cd1";
            style.Interior.Pattern = StyleInteriorPattern.Solid;

        }

        public static void SetDefaultStyle(Workbook Book)
        {
            // Specify which Sheet should be opened and the size of window by default
            Book.ExcelWorkbook.ActiveSheetIndex = 1;

            // Some optional properties of the Document
            Book.Properties.Created = DateTime.Now;

            // Create the Default Style to use for everyone
            WorksheetStyle style = Book.Styles.Add("Default");
            style.Font.FontName = "Tahoma";
            style.Font.Size = 10;

            style = Book.Styles.Add("HeaderStyle");
            style.Font.FontName = "Tacoma";
            style.Font.Size = 10;
            style.Font.Bold = true;
            style.Font.Italic = false;
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Alignment.WrapText = true;
            style.Alignment.Vertical = StyleVerticalAlignment.Top;

            style = Book.Styles.Add("DateStyle");
            style.Font.FontName = "Tacoma";
            style.Font.Size = 10;
            style.Font.Bold = false;
            style.Font.Italic = false;
            style.Alignment.Horizontal = StyleHorizontalAlignment.Left;
            style.Alignment.WrapText = false;
            style.Alignment.Vertical = StyleVerticalAlignment.Top;
            style.NumberFormat = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";

            style = Book.Styles.Add("FooterStyle");
            style.Font.FontName = "Tacoma";
            style.Font.Size = 10;
            style.Font.Bold = false;
            style.Font.Italic = false;

        }

        public static void SendToBrowserForDownload(Workbook book, string filename, HttpResponse Response)
        {
            // Push the file to be downloaded
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);

            book.Save(Response.OutputStream);
            Response.End();
        }
    }
}